﻿CREATE TABLE [dbo].[ApptOrder](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderType] NVARCHAR(50) NULL,
	[BkMemberId] BIGINT NULL,
	[WxUserId] BIGINT NULL,
	[CardType] NVARCHAR(50) NULL,
	[CardNo] NVARCHAR(50) NULL,
	[BranchNo] NVARCHAR(50) NULL,
	[DeptId] NVARCHAR(50) NULL,
	[DeptName] NVARCHAR(50) NULL,
	[DoctorId] NVARCHAR(50) NULL,
	[DoctorName] NVARCHAR(50) NULL,
	[OrderNo] NVARCHAR(50) NOT NULL,
	[RegDate] NVARCHAR(50) NULL,
	[RegNo] NVARCHAR(50) NULL,
	[SuggestBeginTime] NVARCHAR(50) NULL, 
    [SuggestEndTime] NVARCHAR(50) NULL, 
    [Location] NVARCHAR(200) NULL,	
	[OrderStatus] INT NOT NULL,
	[TimeSlice] NVARCHAR(50) NULL,
	[StartTime] NVARCHAR(50) NULL,
	[EndTime] NVARCHAR(50) NULL,
	[RegType] NVARCHAR(50) NULL,
	[PayState] NVARCHAR(50) NULL, 
	[RefundReason] NVARCHAR(50) NULL, 
    [HaltState] NVARCHAR(50) NULL DEFAULT 0,
	[Fee] DECIMAL(18, 2) NULL,
	[TreatFee] DECIMAL(18, 2) NULL,
	[OtherFee] DECIMAL(18, 2) NULL,
	[SumFee] DECIMAL(18, 2) NULL,
	[CreateTime] DATETIME NULL,
	[LockTime] DATETIME NULL,
	[ApptTime] DATETIME NULL,
	[PayTime] DATETIME NULL,
	[FetchTime] DATETIME NULL,
	[SignInTime] DATETIME NULL,
	[RefundTime] DATETIME NULL,
	[CancelByUserTime] DATETIME NULL,
	[CancelByHospTime] DATETIME NULL,	
	[CancelBySysTime] DATETIME NULL, 
	[NextCheckTime] DATETIME NULL,
	[OrderStatusOnSetCheck] INT NULL, 
	[CheckReserved] NVARCHAR(50) NULL , 
	[Remark] NTEXT NULL,        
    [WxTransactionId] NVARCHAR(50) NULL, 
    [WxRefundId] NVARCHAR(50) NULL, 
	[VisitNo] NVARCHAR(50) NULL,     
    [Sqno] NVARCHAR(50) NULL, 	
    CONSTRAINT [PK_ApptOrder] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单类型 0-微信平台预约订单  1-其它平台预约订单 2-微信平台挂号订单' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'OrderType'
GO


GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'医生院内编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'DoctorId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'医生名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'DoctorName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'OrderNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0未锁号 1锁号中 2锁号失败 3已锁号 4取消锁号中 5取消锁号成功 6取消锁号失败 7预约中 8预约成功 9预约失败 10取消预约中 11取消预约成功 12取消预约失败 13支付中 14支付失败 15支付成功 16取号中 17取号失败 18退款中 19退款成功 20退款失败 21取号成功 22 预约报到失败 23 预约报到成功 24 预约报到中 25 微信退号中 26 微信退号失败 27 微信退号成功 28 微信退号退款中 29 微信退号退款失败 30 微信退号退款成功' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'OrderStatus'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'Remark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'挂号预约记录' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder'
GO



EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'BkMember.Id',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'BkMemberId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'卡类型: 0-诊疗卡1-健康卡 2-市民卡 3-未知',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CardType'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'患者信息卡卡号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CardNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'分园区Id',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'BranchNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'科室院内编码',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'DeptId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'科室名称',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'DeptName'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'就诊日期',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RegDate'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，号序',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RegNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'预约总时段：上午，下午，夜间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'TimeSlice'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'分时开始时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'StartTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'分时结束时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'EndTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'号别',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RegType'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'支付状态:0-未付款,1-已付款,2-退款中,3-已退款 4-退款失败',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'PayState'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'挂号费',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'Fee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'诊疗费',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'TreatFee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'其他费用',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'OtherFee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'总费用',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SumFee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'订单创建时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CreateTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'锁号成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'LockTime'
GO

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'用户取消预约的时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CancelByUserTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'医院取消订单时间（停诊）',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'CancelByHospTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'支付成功时间，支付成功后微信返回',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'PayTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'退款成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RefundTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'系统下次检查时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'NextCheckTime'
GO

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'预约成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'ApptTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'FetchTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，建议就诊开始时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SuggestBeginTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，建议就诊结束时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SuggestEndTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，就诊地点',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'Location'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'停诊状态：0-正常，1-已停诊',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'HaltState'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'系统取消预约的时间(超时或系统出错而导致的取消)',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CancelBySysTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'系统检查预留字段',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'CheckReserved'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'退款原因，1-取号失败而退费，2-支付完成时订单已关闭（支付超时）',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RefundReason'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'设置系统下次检查时间时的订单状态',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'OrderStatusOnSetCheck'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'微信支付交易号，支付成功后微信返回',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WxTransactionId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'微信支付退款单号，退款成功后微信返回',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WxRefundId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，就诊流水号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'VisitNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'分时序号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'Sqno'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'WxUser.Id',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WxUserId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'报到时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SignInTime'